import pymysql
from faker import Faker
import random
import faker


class CustomFakerProvider(faker.providers.BaseProvider):
    def __init__(self, locales=None, **kwargs):
        super().__init__(locales, **kwargs)

        # 模拟数据，实际应用时请替换为真实数据源
        self.fujian_areas = [
            {'name': '宁德市', 'code': '350900'},
            {'name': '蕉城区', 'code': '350902'},
            {'name': '古田县', 'code': '350922'},
            {'name': '福鼎市', 'code': '350982'},
            {'name': '霞浦县', 'code': '350921'},
            {'name': '福安市', 'code': '350981'},
            {'name': '柘荣县', 'code': '350926'},
            {'name': '屏南县', 'code': '350923'},
            {'name': '周宁县', 'code': '350925'},
            {'name': '寿宁县', 'code': '350924'},
            {'name': '福州市', 'code': '350100'},
            {'name': '罗源县', 'code': '350123'},
            {'name': '闽清县', 'code': '350124'},
            {'name': '连江县', 'code': '350122'},
            {'name': '福清市', 'code': '350181'},
            {'name': '长乐区', 'code': '350112'},
            {'name': '平潭县', 'code': '350128'},
            {'name': '台江区', 'code': '350103'},
            {'name': '永泰县', 'code': '350125'},
            {'name': '鼓楼区', 'code': '350102'},
            {'name': '晋安区', 'code': '350111'},
            {'name': '闽侯县', 'code': '350121'},
            {'name': '马尾区', 'code': '350105'},
            {'name': '仓山区', 'code': '350104'},
            {'name': '莆田市', 'code': '350300'},
            {'name': '秀屿区', 'code': '350305'},
            {'name': '荔城区', 'code': '350304'},
            {'name': '仙游县', 'code': '350322'},
            {'name': '涵江区', 'code': '350303'},
            {'name': '城厢区', 'code': '350302'},
            {'name': '泉州市', 'code': '350500'},
            {'name': '惠安县', 'code': '350521'},
            {'name': '晋江市', 'code': '350582'},
            {'name': '洛江区', 'code': '350504'},
            {'name': '南安市', 'code': '350583'},
            {'name': '金门县', 'code': '350527'},
            {'name': '石狮市', 'code': '350581'},
            {'name': '泉港区', 'code': '350505'},
            {'name': '永春县', 'code': '350525'},
            {'name': '安溪县', 'code': '350524'},
            {'name': '鲤城区', 'code': '350502'},
            {'name': '德化县', 'code': '350526'},
            {'name': '丰泽区', 'code': '350503'},
            {'name': '厦门市', 'code': '350200'},
            {'name': '湖里区', 'code': '350206'},
            {'name': '思明区', 'code': '350203'},
            {'name': '翔安区', 'code': '350213'},
            {'name': '集美区', 'code': '350211'},
            {'name': '同安区', 'code': '350212'},
            {'name': '海沧区', 'code': '350205'},
            {'name': '漳州市', 'code': '350600'},
            {'name': '龙海区', 'code': '350604'},
            {'name': '漳浦县', 'code': '350623'},
            {'name': '东山县', 'code': '350626'},
            {'name': '南靖县', 'code': '350627'},
            {'name': '平和县', 'code': '350628'},
            {'name': '华安县', 'code': '350629'},
            {'name': '长泰区', 'code': '350605'},
            {'name': '芗城区', 'code': '350602'},
            {'name': '龙文区', 'code': '350603'},
            {'name': '云霄县', 'code': '350622'},
            {'name': '诏安县', 'code': '350624'},
            {'name': '龙岩市', 'code': '350800'},
            {'name': '武平县', 'code': '350824'},
            {'name': '长汀县', 'code': '350821'},
            {'name': '新罗区', 'code': '350802'},
            {'name': '漳平市', 'code': '350881'},
            {'name': '永定区', 'code': '350803'},
            {'name': '连城县', 'code': '350825'},
            {'name': '上杭县', 'code': '350823'},
            {'name': '三明市', 'code': '350400'},
            {'name': '沙县区', 'code': '350405'},
            {'name': '建宁县', 'code': '350430'},
            {'name': '将乐县', 'code': '350428'},
            {'name': '明溪县', 'code': '350421'},
            {'name': '宁化县', 'code': '350424'},
            {'name': '三元区', 'code': '350404'},
            {'name': '清流县', 'code': '350423'},
            {'name': '永安市', 'code': '350481'},
            {'name': '尤溪县', 'code': '350426'},
            {'name': '泰宁县', 'code': '350429'},
            {'name': '大田县', 'code': '350425'},
            {'name': '南平市', 'code': '350700'},
            {'name': '松溪县', 'code': '350724'},
            {'name': '浦城县', 'code': '350722'},
            {'name': '光泽县', 'code': '350723'},
            {'name': '建阳区', 'code': '350703'},
            {'name': '政和县', 'code': '350725'},
            {'name': '建瓯市', 'code': '350783'},
            {'name': '顺昌县', 'code': '350721'},
            {'name': '延平区', 'code': '350702'},
            {'name': '武夷山市', 'code': '350782'},
            {'name': '邵武市', 'code': '350781'}
            # ... 省内所有市县数据 ...
        ]

    def fujian_city(self):
        return self.random_element(self.fujian_areas)["name"]

    def fujian_city_code(self):
        return self.random_element(self.fujian_areas)

    def fujian_citycode(self):
        return self.random_element(self.fujian_areas)["code"]


# 初始化 Faker 实例并添加自定义提供商
fake = faker.Faker()
fake.add_provider(CustomFakerProvider)

# 使用自定义方法生成福建省的市县名称和编码
city_name = fake.fujian_city_code()
# city_code = fake.fujian_city_code()
print(f"城市名称: {city_name}")
# , 编码: {city_code}

# 若要批量生成多个样本
for _ in range(5):
    city_info = {"name": fake.fujian_city(), "code": fake.fujian_city_code()}
    print(city_info)

# 连接数据库
# 连接数据库
# 连接数据库
db = pymysql.connect(host="116.204.71.143", user="fjsbb", password="fjsbb", database="fjsbb")

# 获取所有表名
cursor = db.cursor()
cursor.execute("SHOW TABLES")
tables = [table[0] for table in cursor.fetchall()]
# 换成实际要生成数据的表
tables = ["FJBB_YLWSXTSYRY"]
# 生成测试数据
faker = Faker("zh_CN")
faker.add_provider(CustomFakerProvider)

for table in tables:
    # 获取表结构
    # cursor.execute("DESC %s" % table)
    cursor.execute(
        "SELECT column_name, data_type, column_comment FROM information_schema.COLUMNS WHERE table_name = '%s'" % table)
    columnsAll = [column for column in cursor.fetchall()]
    columns = [column[0] for column in columnsAll]

    # 生成 20 条测试数据
    for i in range(20):
        data = []
        # 根据字段，自定义数据
        for column in columnsAll:
            citycode = faker.fujian_city_code()
            if len(column[0])> 2 and column[0][-2:].lower() == "id":  # 主键
                data.append(faker.uuid4().replace("-", ""))
            elif "int" in column[1] or "_number" in column[0]:   # 指标
                data.append(random.randint(0, 10000))
            ##########################################
            elif "JG_LX" in column[0]:  # 机构类型
                data.append(faker.random_element(["行政机构", "事业单位"]))
            elif "JG_JSJGS" in column[0]:  # 增设机构数
                data.append(random.randint(0, 100))
            elif "JG_HJJGS" in column[0]:  # 核减机构数
                data.append(random.randint(0, 100))
            elif "JG_ZJBZS" in column[0]:  # 增减编制数
                data.append(random.randint(0, 100))
            elif "JG_HJBZS" in column[0]:  # 核减编制数
                data.append(random.randint(0, 100))
            ##########################################
            elif "AREA" in column[0] or "_POS" in column[0] or column[0] == "FJ_ORGTREE_CODE":  # 区域邮编
                data.append(citycode['code'])
            elif len(column) > 3 and "年份" in column[3]: # 年份
                data.append(faker.date_time())
            elif "_NAME" in column[0] or "_POS_NAME" in column[0]:
                data.append(citycode['name'])
            elif "datetime" in column[1] or "date" in column[1]:
                data.append(faker.date_time())
            elif "CERTIFICATE" in column[0]:
                data.append(faker.ssn())
            else:
                # data.append(faker.random_element([faker.name(), faker.email(), faker.address(), faker.phone_number()]))
                data.append(faker.random_element([faker.company_prefix()]))

        # 插入数据
        sql = ("INSERT INTO %s (%s) VALUES (%s)" % (table, ",".join(columns), ",".join(["%s"] * len(data))), data)
        print(sql)
        cursor.execute("INSERT INTO %s (%s) VALUES (%s)" % (table, ",".join(columns), ",".join(["%s"] * len(data))),
                       data)

# 关闭数据库连接
db.commit()
db.close()
